﻿// --------------------------------------------------------------------------------------
// <copyright file="ResourceDAO.cs" company="NGroup">
// Copyright (c) NGroup. All rights reserved.
// </copyright>
// --------------------------------------------------------------------------------------
// <history>
//     <change who="Bui Thanh Nam" date="2014.09.17">Create</change>
// </history>
// --------------------------------------------------------------------------------------

namespace NGroup.VTL.DAL
{
    using System.Collections.Generic;
    using NGroup.VTL.Models;
    using System.Xml.Linq;
    using System.Web;
    using System.Linq;
    using System.Configuration;
    using System.Web.Configuration;
    using System;
    using System.Data.SqlClient;
    using System.Data;
    using NGroup.VTL.Core.Const;

    public class ResourceDAO
    {
        private static readonly string sitemapResourcePath;
        private const string sitemapNamespace = "http://www.sitemaps.org/schemas/sitemap/0.9";

        static ResourceDAO()
        {
            string root = HttpContext.Current.Server.MapPath("~");
            sitemapResourcePath = string.Format("{0}/sitemap.xml", root);
        }

        public List<TranslationModel> GetTranslationsByTypeAndPagingInfo(int type, ref PagingInfoModel pagingInfo)
        {
            return null;
        }

        public bool UpdateTranslationItem(TranslationModel item)
        {
            string sqlTemplate = @"UPDATE [dbo].[Resources]
                                    SET [Value] = N'{0}'                                       
                                    WHERE [Name] = '{1}' AND
                                          [Culture] = '{2}'

                                    UPDATE [dbo].[Resources]
                                    SET [Value] = N'{3}'                                       
                                    WHERE [Name] = '{4}' AND
                                          [Culture] = '{5}'";

            string sqlStatement = string.Format(sqlTemplate, item.English, item.Key, LanguageCodes.en.ToString(), item.Vietnamese, item.Key, LanguageCodes.vi.ToString());

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(sqlStatement, conn);
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();

                return true;
            }
        }

        public List<TranslationModel> GetAllTranslations()
        {
            var resources = new List<TranslationModel>();

            const string sql = "select Culture, Name, Value, Comment from dbo.Resources;";

            using (var con = BaseDAO.GetConnectionString())
            {
                var cmd = new SqlCommand(sql, con);

                con.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    string culture;
                    string key;

                    while (reader.Read())
                    {
                        // Improve later!!!
                        culture = reader["Culture"].ToString();
                        key = reader["Name"].ToString();

                        if (resources.Any(item => item.Key == key))
                        {
                            TranslationModel transItem = resources.Where(
                                                                        item => item.Key == key &&
                                                                        (string.IsNullOrEmpty(item.English) || string.IsNullOrEmpty(item.Vietnamese)))
                                                                        .FirstOrDefault();
                            if (transItem != null)
                            {
                                if (transItem.English == null)
                                {
                                    transItem.English = reader["Value"].ToString();
                                }
                                else if (transItem.Vietnamese == null)
                                {
                                    transItem.Vietnamese = reader["Value"].ToString();
                                }
                            }
                        }
                        else
                        {
                            resources.Add(new TranslationModel
                            {
                                Key = reader["Name"].ToString(),
                                English = (string.Equals(culture, LanguageCodes.en.ToString(), StringComparison.OrdinalIgnoreCase) ? reader["Value"].ToString() : null),
                                Vietnamese = (string.Equals(culture, LanguageCodes.vi.ToString(), StringComparison.OrdinalIgnoreCase) ? reader["Value"].ToString() : null),
                                Comment = reader["Comment"].ToString()
                            });
                        }
                    }

                    if (!reader.HasRows) throw new Exception("No resources were found");
                }
            }

            return resources;
        }

        public void UpdateTranslations(List<TranslationModel> updateList)
        {
        }

        private void CauseRebuild()
        {
            Configuration webConfigApp = WebConfigurationManager.OpenWebConfiguration("~");
            webConfigApp.AppSettings.Settings["TransUpdate"].Value = DateTime.Now.ToString("yyyyMMddHHmmss");
            webConfigApp.Save();
        }

        public bool UpdateSitemapEntry(string key)
        {
            try
            {
                XDocument sitemap = XDocument.Load(sitemapResourcePath);

                XElement targetPostNode = sitemap.Descendants(XName.Get("url", sitemapNamespace)).Where(url => this.Is(url.Descendants(XName.Get("loc", sitemapNamespace)).FirstOrDefault().Value, key)).FirstOrDefault();

                if (targetPostNode != null)
                {
                    targetPostNode.Descendants(XName.Get("lastmod", sitemapNamespace)).FirstOrDefault().Value = DateTime.UtcNow.ToString("yyyy-MM-ddThh:mm:ss+00:00");

                    sitemap.Save(sitemapResourcePath, SaveOptions.None);

                    return true;
                }
            }
            catch
            {
                throw;
            }

            return false;
        }

        public bool RemoveSitemapEntry(string key)
        {
            try
            {
                XDocument sitemap = XDocument.Load(sitemapResourcePath);

                XElement targetPostNode = sitemap.Descendants(XName.Get("url", sitemapNamespace)).Where(url => this.Is(url.Descendants(XName.Get("loc", sitemapNamespace)).FirstOrDefault().Value, key)).FirstOrDefault();

                if (targetPostNode != null)
                {
                    targetPostNode.Remove();

                    sitemap.Save(sitemapResourcePath, SaveOptions.None);

                    return true;
                }
            }
            catch
            {
                throw;
            }

            return false;
        }

        public bool AddSitemapEntry(string key)
        {
            try
            {
                XDocument sitemap = XDocument.Load(sitemapResourcePath);

                XElement templateElement = sitemap.Descendants(XName.Get("url", sitemapNamespace)).Last();

                XElement newNode = new XElement(templateElement);

                newNode.Descendants(XName.Get("loc", sitemapNamespace)).FirstOrDefault().Value = key;
                newNode.Descendants(XName.Get("lastmod", sitemapNamespace)).FirstOrDefault().Value = DateTime.UtcNow.ToString("yyyy-MM-ddThh:mm:ss+00:00");

                templateElement.AddAfterSelf(newNode);

                sitemap.Save(sitemapResourcePath, SaveOptions.None);

                return true;

            }
            catch
            {
                throw;
            }

            return false;
        }

        private bool Is(string source, string substring)
        {
            if (string.IsNullOrEmpty(source))
            {
                return false;
            }

            return source.EndsWith(substring);
        }
    }
}
